Query table data from Server "A" by Server "B"

[Jim Story]

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[B. Jackson]

Another way to do this is using Access 97 and an append query. Set both databases to single user mode to avoid running out of locks. Use ODBC to link the tables.

------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[Jim Story]

I need to set this up using T-SQL within a Job Task. Does anyone know of
T-SQL commands where I can transfer the data again from server "A" to server "B"?


------------
B. Jackson at 3/23/00 2:48:12 PM


Another way to do this is using Access 97 and an append query. Set both databases to single user mode to avoid running out of locks. Use ODBC to link the tables.

------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[B. Jackson]

what version of SQL server are you using? If 6.5 open enterprise manager and look under the tools menu. DatabaseObject transfer. You can schedule transfers there.

------------
Jim Story at 3/23/00 3:15:14 PM

I need to set this up using T-SQL within a Job Task. Does anyone know of
T-SQL commands where I can transfer the data again from server "A" to server "B"?


------------
B. Jackson at 3/23/00 2:48:12 PM


Another way to do this is using Access 97 and an append query. Set both databases to single user mode to avoid running out of locks. Use ODBC to link the tables.

------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[Jim Story]

Do you know of any way to do this through T-SQL scripts?


------------
B. Jackson at 3/23/00 4:02:11 PM


what version of SQL server are you using? If 6.5 open enterprise manager and look under the tools menu. DatabaseObject transfer. You can schedule transfers there.

------------
Jim Story at 3/23/00 3:15:14 PM

I need to set this up using T-SQL within a Job Task. Does anyone know of
T-SQL commands where I can transfer the data again from server "A" to server "B"?


------------
B. Jackson at 3/23/00 2:48:12 PM


Another way to do this is using Access 97 and an append query. Set both databases to single user mode to avoid running out of locks. Use ODBC to link the tables.

------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[B.Jackson]

Check this out. This may help you.


Description
Creates Transact-SQL script file(s) used by the Transfer method for creating copied objects in the destination database. Set the properties of a created Transfer object as desired, pass that Transfer object to the ScriptTransfer method with tScriptMode set to SQLOLEXfrFile_SummaryFiles to create the script files, and then pass the same Transfer object to the Transfer method to perform the database transfer.
Visual Basic
strScript = Database.ScriptTransfer (
[TransferSpec :=] Transfer,
[[ScriptFileMode :=] tScriptMode,]
[[ScriptFilePath :=] strFile] )
C++
HRESULT pDatabase->ScriptTransfer (
LPSQLOLETRANSFER pTransfer,
SQLOLE_XFRSCRIPTMODE_TYPE tScriptMode = SQLOLEXfrFile_Default,
SQLOLE_LPCSTR strFile = NULL,
SQLOLE_LPBSTR pstrScript = NULL );
Elements


Element Type Description

strScript String Returned Transact-SQL script
Transfer Transfer Created Transfer object that specifies how to execute the database transfer
tScriptMode SQLOLE_XFRSCRIPTMODE _TYPE The script transfer type
strFile String If tScriptMode is SQLOLEXfrFile_SingleFile, the full path and filename of the single script file; otherwise, the full path of the directory used to store the script files

Remarks
When tScriptMode is SQLOLEXfrFile_SummaryFiles (the default), multiple script files are created that have names of the following form:
source_server.source_database.ext
where source_server is the name of the SQL Server that contains the source database, source_database is the name of the source database, and ext is one of the following three-letter extensions that are used to create database objects...


File extension Script creates

LGN Logins
GRP Groups, group statement privileges
USR Users, user statement privileges
DEF Non-declarative referential integrity (DRI) defaults
RUL Rules
UDT User-defined datatypes
TAB Tables (does not include DRI)
DR1 Table DRI before copying data (includes clustered primary and unique keys)
DR2 Table DRI after copying data (includes nonclustered primary and unique keys, CHECK constraints, and DRI defaults)
ID1 Table indexes created before copying data (includes clustered non-DRI indexes)
ID2 Table indexes created after copying data (includes nonclustered non-DRI indexes)
BND Bindings for rules and non-DRI defaults
PRV Table privileges
FKY Foreign keys
TRG Triggers
VIW Views and view privileges
PRC Stored procedures and stored procedure privileges

...or one of the following three-letter extensions that are used to drop database objects.


Extension Script drops

DP1 Drops foreign keys that reference copied tables (listed in the file with the DP2 extension). The Transfer method does not run this script. After a transfer is complete, you can run the DP1 script followed by the DP2 script on the source database to drop the source objects.
DP2 Drops all objects to be copied. (DP2 is used on the destination database only when the Transfer.DropDestObjectsFirst property is set to True).

The Database.Transfer method runs these script files in the following order: DP2, LGN, GRP, USR, DEF, RUL, UDT, TAB, DR1, ID1, bulk copy of copied tables, DR2, ID2, BND, PRV, FKY, TRG, VIW, PRC.
To create the transfer scripts for private use (and not for later use with the Transfer method) you can set tScriptMode to SQLOLEXfrFile_SingleFilePerObject or SQLOLEXfrFile_SingleFile.
When tScriptMode is SQLOLEXfrFile_SingleFilePerObject, multiple files are created that have names of the following form:
owner.object.ext
where owner is the username of the object owner, object is the name of the database object, and ext is one of the extensions listed above.
When tScriptMode is SQLOLEXfrFile_SingleFile, a single file (specified in strFile) is created that contains the entire Transact-SQL script for creating the database objects in the destination database.



------------
Jim Story at 3/23/00 4:08:58 PM

Do you know of any way to do this through T-SQL scripts?


------------
B. Jackson at 3/23/00 4:02:11 PM


what version of SQL server are you using? If 6.5 open enterprise manager and look under the tools menu. DatabaseObject transfer. You can schedule transfers there.

------------
Jim Story at 3/23/00 3:15:14 PM

I need to set this up using T-SQL within a Job Task. Does anyone know of
T-SQL commands where I can transfer the data again from server "A" to server "B"?


------------
B. Jackson at 3/23/00 2:48:12 PM


Another way to do this is using Access 97 and an append query. Set both databases to single user mode to avoid running out of locks. Use ODBC to link the tables.

------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[Deepak]

add one of the servers as a linked server to the other and then you can write distributed queries against it.

D.

query ------------
B.Jackson at 3/23/00 4:17:51 PM


Check this out. This may help you.


Description
Creates Transact-SQL script file(s) used by the Transfer method for creating copied objects in the destination database. Set the properties of a created Transfer object as desired, pass that Transfer object to the ScriptTransfer method with tScriptMode set to SQLOLEXfrFile_SummaryFiles to create the script files, and then pass the same Transfer object to the Transfer method to perform the database transfer.
Visual Basic
strScript = Database.ScriptTransfer (
[TransferSpec :=] Transfer,
[[ScriptFileMode :=] tScriptMode,]
[[ScriptFilePath :=] strFile] )
C++
HRESULT pDatabase->ScriptTransfer (
LPSQLOLETRANSFER pTransfer,
SQLOLE_XFRSCRIPTMODE_TYPE tScriptMode = SQLOLEXfrFile_Default,
SQLOLE_LPCSTR strFile = NULL,
SQLOLE_LPBSTR pstrScript = NULL );
Elements


Element Type Description

strScript String Returned Transact-SQL script
Transfer Transfer Created Transfer object that specifies how to execute the database transfer
tScriptMode SQLOLE_XFRSCRIPTMODE _TYPE The script transfer type
strFile String If tScriptMode is SQLOLEXfrFile_SingleFile, the full path and filename of the single script file; otherwise, the full path of the directory used to store the script files

Remarks
When tScriptMode is SQLOLEXfrFile_SummaryFiles (the default), multiple script files are created that have names of the following form:
source_server.source_database.ext
where source_server is the name of the SQL Server that contains the source database, source_database is the name of the source database, and ext is one of the following three-letter extensions that are used to create database objects...


File extension Script creates

LGN Logins
GRP Groups, group statement privileges
USR Users, user statement privileges
DEF Non-declarative referential integrity (DRI) defaults
RUL Rules
UDT User-defined datatypes
TAB Tables (does not include DRI)
DR1 Table DRI before copying data (includes clustered primary and unique keys)
DR2 Table DRI after copying data (includes nonclustered primary and unique keys, CHECK constraints, and DRI defaults)
ID1 Table indexes created before copying data (includes clustered non-DRI indexes)
ID2 Table indexes created after copying data (includes nonclustered non-DRI indexes)
BND Bindings for rules and non-DRI defaults
PRV Table privileges
FKY Foreign keys
TRG Triggers
VIW Views and view privileges
PRC Stored procedures and stored procedure privileges

...or one of the following three-letter extensions that are used to drop database objects.


Extension Script drops

DP1 Drops foreign keys that reference copied tables (listed in the file with the DP2 extension). The Transfer method does not run this script. After a transfer is complete, you can run the DP1 script followed by the DP2 script on the source database to drop the source objects.
DP2 Drops all objects to be copied. (DP2 is used on the destination database only when the Transfer.DropDestObjectsFirst property is set to True).

The Database.Transfer method runs these script files in the following order: DP2, LGN, GRP, USR, DEF, RUL, UDT, TAB, DR1, ID1, bulk copy of copied tables, DR2, ID2, BND, PRV, FKY, TRG, VIW, PRC.
To create the transfer scripts for private use (and not for later use with the Transfer method) you can set tScriptMode to SQLOLEXfrFile_SingleFilePerObject or SQLOLEXfrFile_SingleFile.
When tScriptMode is SQLOLEXfrFile_SingleFilePerObject, multiple files are created that have names of the following form:
owner.object.ext
where owner is the username of the object owner, object is the name of the database object, and ext is one of the extensions listed above.
When tScriptMode is SQLOLEXfrFile_SingleFile, a single file (specified in strFile) is created that contains the entire Transact-SQL script for creating the database objects in the destination database.



------------
Jim Story at 3/23/00 4:08:58 PM

Do you know of any way to do this through T-SQL scripts?


------------
B. Jackson at 3/23/00 4:02:11 PM


what version of SQL server are you using? If 6.5 open enterprise manager and look under the tools menu. DatabaseObject transfer. You can schedule transfers there.

------------
Jim Story at 3/23/00 3:15:14 PM

I need to set this up using T-SQL within a Job Task. Does anyone know of
T-SQL commands where I can transfer the data again from server "A" to server "B"?


------------
B. Jackson at 3/23/00 2:48:12 PM


Another way to do this is using Access 97 and an append query. Set both databases to single user mode to avoid running out of locks. Use ODBC to link the tables.

------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

[RamaKrishna Seelam]

Try this:

Step 1. Register both the servers as Remote Servers mutually (Thru Enterprise manager or thru 'sp_addserver&#39.
Step 2. Write a Stored Proc (SP) in Server A embedding the select statement.You could parmeterise any runtime constants to be used in the where clause.
Step 3. Call this Remote SP from Server B either directly or from within another SP as the need may be. The syntax is as follows:
exec ServerA.DatabaseName.dbo.SPName

I believe this is the cleanest way.

Hope this helps.
Regards,
Ramakrishna Seelam




------------
Jim Story at 3/23/00 2:09:03 PM

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?